This forum is closed to new posts and responses. Individual names altered for privacy purposes. The information contained in this website is provided for informational purposes only and should not be construed as a forum for customer support requests. Any customer support requests should be directed to the official HCL customer support channels below:

HCL Software Customer Support Portal for U.S. Federal Government clients
HCL Software Customer Support Portal


Dec 8, 2014, 6:25 PM
3 Posts

Exporting from IBM Notes9 to Excel 2013

  • Category: Domino Designer
  • Platform: Windows
  • Release: 9.0.1
  • Role: Administrator
  • Tags:
  • Replies: 5

A developer composed this script a few years back, which works just fine with any version of Excel OLDER than 2013. It only produces the first record, when there are many. Can anyone spot what might be missing, or if you think it could be as simple as changing an options setting in Excel 2013?:

 

Option Public
Const xlLeft=-4131
Const xlRight=-4152
Const xlTop=-4160
Const xlBottom=-4107
Const xlCenter=-4108
Const xlThin=2
Const xlHairline=1
Const xlNone=-4142
Const xlAutomatic=-4105

Const xlPortrait=1
Const xlLandscape=2
Sub Initialize
    
    Dim session As New Notessession
    Dim excelArange As String
    Dim excelBrange As String
    Dim excelCrange As String
    Dim excelDrange As String
    Dim excelErange As String
    Dim excelFrange As String
    
    Dim excelGrange As String
    Dim excelHrange As String
    Dim excelIrange As String
    Dim excelJrange As String
    Dim excelKrange As String
    Dim excelLrange As String
    
    Dim excelMrange As String
    Dim excelNrange As String
    Dim excelOrange As String
    Dim excelPrange As String
    Dim excelQrange As String
    Dim excelRrange As String
    
    Dim db As NotesDatabase
    Dim ws As New Notesuiworkspace  ' NV
    Dim uiview As Notesuiview  ' NV
    
    
    Dim j As Integer
    
    Dim excelSrange As String
    Dim dateTime As NotesDateTime
    Set dateTime = New NotesDateTime( "Today" )
    when$ = dateTime.Dateonly
    
    Dim rssdb As NotesDatabase
    Dim rssvw As NotesView
    Dim rssdoc As NotesDocument
    
'check for user location. See if "" works for either location
    Set rssdb = session.getdatabase( "Notes3","Development/specrev.nsf",False) 'change to prod file name
    
    If Not rssdb.IsOpen Then
        Msgbox "Error; Unable to find Spec Sheet database."
        Goto Leave
    End If
    
    Set uiview = ws.Currentview  ' NV
    Dim myVw As NotesView
    Set myVw = uiview.View  ' NV
    Dim nvec As Notesviewentrycollection  ' NV
    'Set nvec = myVw.Getallentriesbykey  ' NV
    ' OR
    Set nvec = myVw.AllEntries  ' NV
    
    Set db = session.currentdatabase
    Set rssvw = rssdb.GetView("(RSSIDLookup)")
    
    ' DC Set dc = db.unprocessedDocuments
    Dim ent As NotesViewEntry  ' NV
    Set ent = nvec.GetFirstEntry()  ' NV
    Set doc = ent.document  ' NV
    ' DC Set doc = dc.getnthdocument(dc.count)
    
    If doc Is Nothing Then
        Msgbox "No Documents to Export"
        Goto Leave
    End If
    
    Set xlApp = createObject("Excel.application")
    On Error Goto Leave
    xlapp.visible=True
    xlapp.workbooks.add
    ' Column names
    
    Set xlsheet = xlapp.workbooks(1).worksheets(1)
    With xlsheet
        .range("D1").Value = "SAMPLES STATUS REPORT AS OF " & when$        
        .range("A3").Value = "#"
        .range("B3").Value = "ITEM #"
        .range("C3").Value = "DESCRIPTION"        
        .range("D3").Value = "SKETCH"
        .range("E3").Value = "DRAWING DATE"
        .range("F3").Value = "DATE DELIVERD TO VENDOR"        
        
        .range("G3").Value = "VENDOR RECEIPT NUMBER"
        .range("H3").Value = "VENDOR"        
        .range("I3").Value = "PO #"        
        .range("J3").Value = "PRICE"
        .range("K3").Value = "QTY"
        .range("L3").Value = "DIMENSION"    
        
        .range("M3").Value = "MATERIAL"
        .range("N3").Value = "SOLIDS"        
        .range("O3").Value = "VENEER"        
        .range("P3").Value = "LEATHER"
        .range("Q3").Value = "STONE/MARBLE"
        .range("R3").Value = "REMARKS"    
        
        .range("S3").Value = "STATUS"
        
    End With
    
    m=1
    
    Dim rtitem As Variant
    Dim fc As Integer
    fc = 0    
    
    Set fs = CreateObject("Scripting.FileSystemObject")
    
    xlapp.rows("4:3000").select
    xlapp.selection.RowHeight = 40.5
    xlapp.selection.WrapText = True
    xlapp.rows("3:3000").select
    xlapp.selection.HorizontalAlignment = xlCenter
    xlapp.selection.VerticalAlignment = xlCenter
    
    For j=1 To nvec.count  ' NV
    ' DC For j=1 To dc.count
        Set ent = nvec.GetNthEntry(j)  ' NV
        Set doc = ent.document ' NV
        ' DC Set doc = dc.getnthdocument(j)
        Set rssdoc = rssvw.Getdocumentbykey(doc.RSSId(0),True)
        If rssdoc Is Nothing Then
            Set itemvw = rssdb.GetView("(SKULookup)")
            Set rssdoc = itemvw.Getdocumentbykey(doc.itemkey(0),True)
        End If
        If j=1 And m=1 Then
            temp = j+3 
            m = 2
            j=1
        Else
            temp = temp+1
        End If        
        ExcelARange = "A" &Trim(Str(temp))
        ExcelBRange = "B" &Trim(Str(temp))
        ExcelCRange = "C" &Trim(Str(temp))
        ExcelDRange = "D" &Trim(Str(temp))
        ExcelERange = "E" &Trim(Str(temp))
        ExcelFRange = "F" &Trim(Str(temp))
        
        ExcelGRange = "G" &Trim(Str(temp))
        ExcelHRange = "H" &Trim(Str(temp))
        ExcelIRange = "I" &Trim(Str(temp))
        ExcelJRange = "J" &Trim(Str(temp))
        ExcelKRange = "K" &Trim(Str(temp))
        ExcelLRange = "L" &Trim(Str(temp))
        
        ExcelMRange = "M" &Trim(Str(temp))
        ExcelNRange = "N" &Trim(Str(temp))
        ExcelORange = "O" &Trim(Str(temp))
        ExcelPRange = "P" &Trim(Str(temp))
        ExcelQRange = "Q" &Trim(Str(temp))
        ExcelRRange = "R" &Trim(Str(temp))
        
        ExcelSRange = "S" &Trim(Str(temp))
        
            '<<<<<<<<<<<<<<<<<<<DATA COLLECTION>>>>>>>>>>>>>>>>>>
        
        With xlsheet
            .Range(excelArange).value = j
            .Range(excelBrange).value = doc.ItemKey(0)            
            .Range(excelCrange).value = doc.itemdesc(0)            
            .Range(excelDrange).value = "" 'doc.GetFirstitem("MyRTF")          
            .Range(excelErange).value = Cstr(doc.DetailDate(0))
            .Range(excelFrange).value = Cstr(doc.DetailRcvVen(0))
            .Range(excelGrange).value = "" 'doc.(0) vendor reciept number remove    
            
            .Range(excelHrange).value = doc.Vendor(0)            
            .Range(excelIrange).value = doc.PONum(0)            
            .Range(excelJrange).value = "" 'doc.(0) User to populate            
            .Range(excelKrange).value = Cstr(doc.QtyOrdered(0))
            
            .Range(excelMrange).value = doc.ItemMaterial(0)
            
            .Range(excelPrange).value = "" 'doc.(0) User to Add            
            .Range(excelQrange).value = "" 'doc.(0) User to Add            
            .Range(excelRrange).value = doc.OvsOffComments(0) '"Remarks" 'doc.(0)
            .Range(excelSrange).value = "" 'doc.(0)
            
            If Not rssdoc Is Nothing Then    
                .Range(excelLrange).value = "W: " & rssdoc.Width(0) & " D: " & rssdoc.Depth(0) & " H: " & rssdoc.Height(0)
                .Range(excelNrange).value = rssdoc.Solids(0)
                .Range(excelOrange).value = rssdoc.Veneers(0)
                
                Set rtitem = rssdoc.GetFirstItem( "RSSAttachment" )
                If ( rtitem.Type = RICHTEXT ) And rssdoc.Hasembedded Then
                    Forall obj In rtitem.EmbeddedObjects                        
                        fc = fc + 1
                        Call obj.ExtractFile _
                        ( "c:\windows\temp\" & Cstr(fc) & ".jpg" )
                        
                        picpath$ = "C:\windows\Temp\*.jpg"
                        searchname = Dir(picpath$)
                        
                        pathname$ = "C:\windows\Temp\" & searchname        
                        xlsheet.cells(temp,4).select
                        xlsheet.Pictures.Insert(pathname$).Select 
                        xlsheet.Pictures.ShapeRange.LockAspectRatio = msoTrue
                        'xlsheet.Pictures.ShapeRange.ScaleWidth 0.47, msoFalse, msoScaleFromTopLeft 
                        'xlsheet.Pictures.ShapeRange.ScaleHeight 0.47, msoFalse, msoScaleFromTopLeft 
                        xlsheet.Pictures.ShapeRange.Height = 40%
                        xlsheet.Pictures.ShapeRange.Width = 40%
                        'xlsheet.Pictures.ShapeRange.Align msoAlignCenters, False
                        Set mypic = fs.GetFile(pathname$)
                        mypic.Delete False
                        
                    End Forall
                End If                
            Else 
            End If            
        End With        
        On Error Resume Next
    Next
    
    '<<<<<<<<<<<<<<<<<<<FORMATTING>>>>>>>>>>>>>>>>>>
    
    xlapp.columns("A:C").select
    xlapp.selection.columns.autofit
    xlapp.selection.font.name="arial"
    xlapp.selection.font.size = 8
    
    xlapp.columns("D").select
    xlapp.selection.font.name="arial"
    xlapp.selection.font.size = 8
    
    xlapp.columns("E:S").select
    xlapp.selection.columns.autofit
    xlapp.selection.font.name="arial"
    xlapp.selection.font.size = 8
    
    xlapp.rows("1:1").select
    xlapp.selection.font.bold=True
    xlapp.selection.font.italic=True
    xlapp.worksheets(1).pagesetup.orientation=1
    xlapp.selection.font.name="arial"
    xlapp.selection.font.size = 10
    
    
    xlapp.rows("3:3").select
    xlapp.selection.font.bold=True
    
    xlapp.worksheets(1).pagesetup.orientation=1
    xlapp.selection.font.name="arial"
    xlapp.selection.font.size = 8
    
    With xlapp.worksheets(1)
        .pagesetup.rightfooter="Page &P" & Chr$(13) & "Date: &D"
        .pagesetup.Centerfooter = ""
    End With
    
    xlsheet.cells(3,3).select
    'xlapp.selection.MergeCells = True
    
    
' <<<<<<<<<<<<<<<<<< REMOVE COMMENT FOR AUTO SAVE >>>>>>>>>>>>>>>>>
    'Find a name for it
    'filename = "OffsetExport" 
    'fileext = ".xls"
    'filefullname = filename + fileext
    
    'installpath$ =  "\\Calvin\Credit\Offset Exports\"   + filefullname
    'searchname = Dir$(installpath$)
    
    'Do While searchname <> ""
    
    '    k = k + 1
    
    '    filefullname = filename + Cstr(k) + fileext
    
    '    installpath$ =  "\\Calvin\Credit\Offset Exports\" + filefullname
    '    searchname = Dir$(installpath$)
    
    'Loop
    
    'Dim installpath As String
    
    'installpath = Inputbox$("Enter the file name and path where you want to place the worksheet."_
    '& Chr(10) & " Example: C:\Windows\Desktop\MyFileName.xls" )
    
    'Kill installpath    
    
    'xlapp.activeworkbook.saveas installpath
    
' <<<<<<<<<<<<<<<<<< END AUTO SAVE >>>>>>>>>>>>>>>>>    
    
Leave:
    
    'xlapp.activeworkbook.Close(1)
    'xlapp.quit
    
    Set xlapp = Nothing
    Set db = Nothing
    Set doc = Nothing    
    ' DC Set dc = Nothing
    
    Set ws = Nothing ' NV
    Set uiview = Nothing  ' NV
    Set myView = Nothing  ' NV
    Set nvec = Nothing  ' NV
    Set ent = Nothing  ' NV
    
    Set xlApp = Nothing
    Set xlsheet = Nothing
    Set fs = Nothing
    Set mypic = Nothing
    Set rssdb = Nothing
    Set rssvw = Nothing
    Set itemvw = Nothing
    Set rssdoc = Nothing
    
End Sub

Dec 8, 2014, 7:33 PM
100 Posts
What does the debugger tell you?

When you run with the debugger on, what does it tell you?

We're just moving to 2013 but generally if you can get one row of data into Excel, then it's not an Excel problem but an issue with the LS code; maybe your doc collection isn't right or something else.

 

Dec 8, 2014, 7:51 PM
3 Posts
Exporting from IBM Notes9 to Excel 2013

I haven't ran Debugger since there are no error messages. But why would it work just fine in previous versions of Excel, just not 2013?

Dec 8, 2014, 10:15 PM
202 Posts
Debugger can help you more than just figuring out an error
it can help you figure out either logic errors, or results that you don't expect.

By stepping through you code, you can see if your code is finding any documents to include in Excel, if it isn't, you can then look at the values to figure out why not.
Dec 11, 2014, 2:52 PM
3 Posts
Debugger not helping

Turned it on and stepped through the code with no problems showing up. I know there has to be a difference between Excel 2010 and Excel 2013, just don't know what it could be, Any other thoughts?

Dec 11, 2014, 8:56 PM
6 Posts
Excel 2013 vs 2010

I have lots of code which similarly runs on machine with different versions of Excel.

I took your code.. made a few changes to use a db i have (since the code is pretty straightforward.. spinning thru some views and documents and outputting stuff into excel).

A couple things I'll note in your code..

there's an On error statement ( 'On Error Goto Leave') will just exit your code if there's any notes errors .. which is probably why you're not 'seeing' any notes error when running.  I commented that out in my test so, i could observe the notes issue where it occurred.

the problem which you may be having may in fact be related to the database/data you're running on.. there's a section of code ..

-----------
Set rtitem = rssdoc.GetFirstItem( "RSSAttachment" )
If ( rtitem.Type = RICHTEXT ) And rssdoc.Hasembedded Then
----------

where.. if for some reason that item (RSSAttachment) isn't in the rssdoc, the next IF statement fail.. and with your On Error statement in place, would just exit.. with no messages.

best to check to make sure the item exists before proceeding.. ala (encapsulating the if logic)

----------
Set rtitem = rssdoc.GetFirstItem( "RSSAttachment" )
If Not(rtitem Is Nothing) Then
     If ( rtitem.Type = RICHTEXT ) And rssdoc.Hasembedded Then
          ForAll obj In rtitem.EmbeddedObjects  
.
.
     End If
End If
--------

so.. i ran your code with my minor changes to refer to different views and docs and it ran fine on 2 different machines.. one running Excel/2013 (64bit) and one running Excel/2010 (32bit)

I'm guessing that you're running this on different machines.. looking at your code.. either the db's are different .. or the other possible place where there might be an error raised if where you're trying to extract an attachment and put it in c:\Windows\temp and that directory doesn't exist or the user doesn't have authority to write to that directory.

Hope that helps.  I don't think it's an issue of /2013 vs /2010

Scott


This forum is closed to new posts and responses. Individual names altered for privacy purposes. The information contained in this website is provided for informational purposes only and should not be construed as a forum for customer support requests. Any customer support requests should be directed to the official HCL customer support channels below:

HCL Software Customer Support Portal for U.S. Federal Government clients
HCL Software Customer Support Portal